/*
 * frmPersonalStamm.java
 *
 * Created on 2. März 2008, 08:12
 */

package com.googlecode.oskis.admin;
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.DefaultListModel;
import com.googlecode.oskis.library.misc.ColorConstants;
import com.googlecode.oskis.library.date.DateHelper;
import com.googlecode.oskis.library.date.NOW;
import com.googlecode.oskis.library.db.DBQuery;
import com.googlecode.oskis.library.db.ComboBoxFiller;
import com.googlecode.oskis.library.db.ListBoxFiller;
import com.googlecode.oskis.library.db.ComboBoxEintrag;
import com.googlecode.oskis.library.db.SNO;
import com.googlecode.oskis.library.db.SNONotFoundException;
import com.googlecode.oskis.library.db.SQLErrorDialog;
import com.googlecode.oskis.library.db.PLZFinder;

/**
 * Dialog zum Bearbeiten des Personalstammes
 * @author  thomas
 */
public class frmPersonalStamm extends javax.swing.JFrame {
    
    
    private Connection db;
    private DBQuery q;
    
    /** Creates new form frmPersonalStamm */
    public frmPersonalStamm(Connection db) {
        initComponents();
        this.db=db;
        NOW now=new NOW();
        String n="to_timestamp('"+now.getFormattedDate()+"','DD.MM.YYYY HH24:MI:SS')";
        q=new DBQuery(db);
        ComboBoxFiller cbf = new ComboBoxFiller(db);
        cbf.fill(cmbGeschlecht, "select geschlecht,bezeichnung " +
                "                from xgeschlecht" +
                "                where "+n+" between datf and datt");
        cbf.fill(cmbAnrede, "select anrede,bezeichnung " +
                "                from xanrede" +
                "                where "+n+" between datf and datt"); 
        cbf.fill(cmbTitel, "select titel,bezeichnung " +
                "                from xtitel" +
                "                where "+n+" between datf and datt"); 
        cbf.fill(cmbLand, "select land,bezeichnung " +
                "                from xland" +
                "                where "+n+" between datf and datt");  
        refreshTable();
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        lblPersonalid = new javax.swing.JLabel();
        txtPersonalid = new javax.swing.JTextField();
        lblName = new javax.swing.JLabel();
        lblVorname = new javax.swing.JLabel();
        lblGeburtsdatum = new javax.swing.JLabel();
        lblGeschlecht = new javax.swing.JLabel();
        lblAnrede = new javax.swing.JLabel();
        lblTitel = new javax.swing.JLabel();
        lblLand = new javax.swing.JLabel();
        lblPLZ = new javax.swing.JLabel();
        lblOrt = new javax.swing.JLabel();
        lblStrasse = new javax.swing.JLabel();
        txtName = new javax.swing.JTextField();
        txtVorname = new javax.swing.JTextField();
        txtGeburtsdatum = new javax.swing.JTextField();
        cmbGeschlecht = new javax.swing.JComboBox();
        cmbAnrede = new javax.swing.JComboBox();
        cmbTitel = new javax.swing.JComboBox();
        cmbLand = new javax.swing.JComboBox();
        txtPLZ = new javax.swing.JTextField();
        txtOrt = new javax.swing.JTextField();
        txtStrasse = new javax.swing.JTextField();
        jScrollPane1 = new javax.swing.JScrollPane();
        tblPersonal = new javax.swing.JTable();
        btnNew = new javax.swing.JButton();
        btnEdit = new javax.swing.JButton();
        btnDelete = new javax.swing.JButton();
        lblDatf = new javax.swing.JLabel();
        lblDatt = new javax.swing.JLabel();
        txtDatf = new javax.swing.JTextField();
        txtDatt = new javax.swing.JTextField();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
        setTitle("Personalstamm");

        lblPersonalid.setText("Personal ID");

        txtPersonalid.setNextFocusableComponent(txtName);
        txtPersonalid.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtPersonalidFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtPersonalidFocusLost(evt);
            }
        });

        lblName.setText("Name");

        lblVorname.setText("Vorname");

        lblGeburtsdatum.setText("Geburtsdatum");

        lblGeschlecht.setText("Geschlecht");

        lblAnrede.setText("Anrede");

        lblTitel.setText("Titel");

        lblLand.setText("Land");

        lblPLZ.setText("PLZ");

        lblOrt.setText("Ort");

        lblStrasse.setText("Strasse");

        txtName.setNextFocusableComponent(txtVorname);
        txtName.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtNameFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtNameFocusLost(evt);
            }
        });

        txtVorname.setNextFocusableComponent(txtGeburtsdatum);
        txtVorname.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtVornameFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtVornameFocusLost(evt);
            }
        });

        txtGeburtsdatum.setNextFocusableComponent(cmbGeschlecht);
        txtGeburtsdatum.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtGeburtsdatumFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtGeburtsdatumFocusLost(evt);
            }
        });

        cmbGeschlecht.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        cmbGeschlecht.setNextFocusableComponent(cmbAnrede);

        cmbAnrede.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        cmbAnrede.setNextFocusableComponent(cmbTitel);

        cmbTitel.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        cmbTitel.setNextFocusableComponent(cmbLand);

        cmbLand.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        cmbLand.setNextFocusableComponent(txtPLZ);

        txtPLZ.setNextFocusableComponent(txtOrt);
        txtPLZ.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtPLZFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtPLZFocusLost(evt);
            }
        });

        txtOrt.setNextFocusableComponent(txtStrasse);
        txtOrt.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtOrtFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtOrtFocusLost(evt);
            }
        });

        txtStrasse.setNextFocusableComponent(txtDatf);
        txtStrasse.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtStrasseFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtStrasseFocusLost(evt);
            }
        });

        tblPersonal.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        tblPersonal.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                tblPersonalMouseClicked(evt);
            }
        });
        jScrollPane1.setViewportView(tblPersonal);

        btnNew.setMnemonic('N');
        btnNew.setText("Neu");
        btnNew.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnNewActionPerformed(evt);
            }
        });

        btnEdit.setMnemonic('E');
        btnEdit.setText("Ändern");
        btnEdit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnEditActionPerformed(evt);
            }
        });

        btnDelete.setMnemonic('L');
        btnDelete.setText("Löschen");
        btnDelete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnDeleteActionPerformed(evt);
            }
        });

        lblDatf.setText("Gült. von");

        lblDatt.setText("Gült. bis");

        txtDatf.setNextFocusableComponent(txtDatt);
        txtDatf.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtDatfFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtDatfFocusLost(evt);
            }
        });

        txtDatt.setNextFocusableComponent(txtPersonalid);
        txtDatt.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtDattFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtDattFocusLost(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(lblGeburtsdatum)
                            .addComponent(lblGeschlecht)
                            .addComponent(lblAnrede)
                            .addComponent(lblTitel)
                            .addComponent(lblLand)
                            .addComponent(lblPLZ)
                            .addComponent(lblOrt)
                            .addComponent(lblStrasse)
                            .addComponent(lblPersonalid)
                            .addComponent(lblName)
                            .addComponent(lblVorname)
                            .addComponent(lblDatf)
                            .addComponent(lblDatt))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(txtDatt)
                            .addComponent(txtDatf)
                            .addComponent(txtStrasse)
                            .addComponent(txtOrt)
                            .addComponent(txtPLZ)
                            .addComponent(cmbLand, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(cmbTitel, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(cmbAnrede, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(cmbGeschlecht, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(txtGeburtsdatum)
                            .addComponent(txtVorname)
                            .addComponent(txtName)
                            .addComponent(txtPersonalid, javax.swing.GroupLayout.DEFAULT_SIZE, 359, Short.MAX_VALUE)))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(btnNew)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnEdit)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnDelete))
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblPersonalid)
                    .addComponent(txtPersonalid, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblName)
                    .addComponent(txtName, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblVorname)
                    .addComponent(txtVorname, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblGeburtsdatum)
                    .addComponent(txtGeburtsdatum, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblGeschlecht)
                    .addComponent(cmbGeschlecht, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblAnrede)
                    .addComponent(cmbAnrede, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblTitel)
                    .addComponent(cmbTitel, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblLand)
                    .addComponent(cmbLand, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblPLZ)
                    .addComponent(txtPLZ, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblOrt)
                    .addComponent(txtOrt, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblStrasse)
                    .addComponent(txtStrasse, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblDatf)
                    .addComponent(txtDatf, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblDatt)
                    .addComponent(txtDatt, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnNew)
                    .addComponent(btnEdit)
                    .addComponent(btnDelete))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 315, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(22, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    
    
    private void refreshTable() {
        NOW now=new NOW();
        String n="to_timestamp('"+now.getFormattedDate()+"','DD.MM.YYYY HH24:MI:SS')";        
        String SQL="select personalid,anrede||' '|| name||', '||vorname as name,geburtsdatum from xpersonal where "+n+" between datf and datt";
        if (q.executeQuery(SQL)) {
            tblPersonal.setModel(q.getTableModel());
        } else {
            JOptionPane.showMessageDialog(this,q.getSQLError());
        }
    }
    
    
    
    private void txtPersonalidFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtPersonalidFocusGained
        txtPersonalid.selectAll();
        txtPersonalid.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtPersonalidFocusGained

    private void txtNameFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtNameFocusGained
        txtName.selectAll();
        txtName.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtNameFocusGained

    private void txtVornameFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtVornameFocusGained
        txtVorname.selectAll();
        txtVorname.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtVornameFocusGained

    private void txtGeburtsdatumFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtGeburtsdatumFocusGained
        txtGeburtsdatum.selectAll();
        txtGeburtsdatum.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtGeburtsdatumFocusGained

    private void txtPLZFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtPLZFocusGained
        txtPLZ.selectAll();
        txtPLZ.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtPLZFocusGained

    private void txtOrtFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtOrtFocusGained
        txtOrt.selectAll();
        txtOrt.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtOrtFocusGained

    private void txtStrasseFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtStrasseFocusGained
        txtStrasse.selectAll();
        txtStrasse.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtStrasseFocusGained

    private void txtDatfFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDatfFocusGained
        txtDatf.selectAll();
        txtDatf.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtDatfFocusGained

    private void txtDattFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDattFocusGained
        txtDatt.selectAll();
        txtDatt.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtDattFocusGained

    private void txtPersonalidFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtPersonalidFocusLost
        txtPersonalid.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtPersonalidFocusLost

    private void txtNameFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtNameFocusLost
        txtName.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtNameFocusLost

    private void txtVornameFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtVornameFocusLost
        txtVorname.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtVornameFocusLost

    private void txtGeburtsdatumFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtGeburtsdatumFocusLost
        DateHelper dh=new DateHelper(txtGeburtsdatum.getText());
        dh.setFormatString("dd.MM.yyyy");
        if (dh.getFormatSuccess()) {
            txtGeburtsdatum.setText(dh.getFormattedDate());
        }
        txtGeburtsdatum.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtGeburtsdatumFocusLost

    private void txtPLZFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtPLZFocusLost
        txtOrt.setText(new PLZFinder(db,txtPLZ.getText()).getOrt());
        txtPLZ.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtPLZFocusLost

    private void txtOrtFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtOrtFocusLost
        txtOrt.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtOrtFocusLost

    private void txtStrasseFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtStrasseFocusLost
        txtStrasse.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtStrasseFocusLost

    private void txtDatfFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDatfFocusLost
        DateHelper dh=new DateHelper(txtDatf.getText());
        dh.setFormatString("dd.MM.yyyy HH:mm:ss");
        if (dh.getFormatSuccess()) {
            txtDatf.setText(dh.getFormattedDate());
        }
        txtDatf.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtDatfFocusLost

    private void txtDattFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDattFocusLost
        DateHelper dh=new DateHelper(txtDatt.getText());
        dh.setFormatString("dd.MM.yyyy HH:mm:ss");
        if (dh.getFormatSuccess()) {
            txtDatt.setText(dh.getFormattedDate());
        }
        txtDatt.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtDattFocusLost

    private void btnNewActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnNewActionPerformed
        int pid;
        try {
            pid=new SNO(db,"personalid").getNewSNO();
        } catch (SNONotFoundException e) {
            JOptionPane.showMessageDialog(this, e.toString());
            return;
        }
        String personalid=String.valueOf(pid);
        txtPersonalid.setText(personalid);
        String name=txtName.getText();
        String vorname=txtVorname.getText();
        String geburtsdatum=txtGeburtsdatum.getText();
        String geschlecht=((ComboBoxEintrag)cmbGeschlecht.getSelectedItem()).getKey();
        String anrede=((ComboBoxEintrag)cmbAnrede.getSelectedItem()).getKey();
        String titel=((ComboBoxEintrag)cmbTitel.getSelectedItem()).getKey();
        String land=((ComboBoxEintrag)cmbLand.getSelectedItem()).getKey();
        String plz=txtPLZ.getText();
        String ort=txtOrt.getText();
        String strasse=txtStrasse.getText();
        String datf=txtDatf.getText();
        String datt=txtDatt.getText();
        String SQL="insert into xpersonal" +
                "   (personalid,name,vorname,geburtsdatum,geschlecht,anrede,titel,land,plz,ort,strasse,datf,datt)" +
                "   values("+personalid+",'"+name+"','"+vorname+"',to_date('"+geburtsdatum+"','DD.MM.YYYY')," +
                "   '"+geschlecht+"','"+anrede+"','"+titel+"','"+land+"','"+plz+"','"+ort+"','"+strasse+"',"+
                "   to_timestamp('"+datf+"','DD.MM.YYYY HH24:MI:SS'),to_timestamp('"+datt+"','DD.MM.YYYY HH24:MI:SS'))";
        try {
            Statement st=db.createStatement();
            st.execute(SQL);                 
            st.close();
            refreshTable();
        } catch (SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
}//GEN-LAST:event_btnNewActionPerformed

    private void tblPersonalMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_tblPersonalMouseClicked
        int row=tblPersonal.getSelectedRow();
        String personalid=(String)tblPersonal.getValueAt(row,0);
        String SQL="select name,vorname,to_char(geburtsdatum,'DD.MM.YYYY') as geburtsdatum,geschlecht,anrede,titel,land,plz," +
                "          ort,strasse," +
                "          to_char(datf,'DD.MM.YYYY HH24:MI:SS') as datf, to_char(datt,'DD.MM.YYYY HH24:MI:SS') as datt" +
                "   from xpersonal " +
                "   where personalid="+personalid;
        try {
            Statement st=db.createStatement();
            ResultSet rs=st.executeQuery(SQL);
            if (rs.next()) {
                txtPersonalid.setText(personalid);
                txtName.setText(rs.getString("name"));
                txtVorname.setText(rs.getString("vorname"));
                txtGeburtsdatum.setText(rs.getString("geburtsdatum"));
                cmbGeschlecht.setSelectedItem(new ComboBoxEintrag(rs.getString("geschlecht"),""));
                cmbAnrede.setSelectedItem(new ComboBoxEintrag(rs.getString("anrede"),""));
                cmbTitel.setSelectedItem(new ComboBoxEintrag(rs.getString("titel"),""));
                cmbLand.setSelectedItem(new ComboBoxEintrag(rs.getString("land"),""));
                txtPLZ.setText(rs.getString("plz"));
                txtOrt.setText(rs.getString("ort"));
                txtStrasse.setText(rs.getString("strasse"));
                txtDatf.setText(rs.getString("datf"));
                txtDatt.setText(rs.getString("datt"));
            } else {
                JOptionPane.showMessageDialog(this,"Personal ID "+personalid+" exitiert nicht");
            }
            st.close();
        } catch (SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
    }//GEN-LAST:event_tblPersonalMouseClicked

    private void btnEditActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnEditActionPerformed
        String personalid=txtPersonalid.getText();
        String name=txtName.getText();
        String vorname=txtVorname.getText();
        String geburtsdatum=txtGeburtsdatum.getText();
        String geschlecht=((ComboBoxEintrag)cmbGeschlecht.getSelectedItem()).getKey();
        String anrede=((ComboBoxEintrag)cmbAnrede.getSelectedItem()).getKey();
        String titel=((ComboBoxEintrag)cmbTitel.getSelectedItem()).getKey();
        String land=((ComboBoxEintrag)cmbLand.getSelectedItem()).getKey();
        String plz=txtPLZ.getText();
        String ort=txtOrt.getText();
        String strasse=txtStrasse.getText();
        String datf=txtDatf.getText();
        String datt=txtDatt.getText();
        String SQL="update xpersonal" +
                "   set name='"+name+"', vorname='"+vorname+"', geburtsdatum=to_date('"+geburtsdatum+"','DD.MM.YYYY')," +
                "       geschlecht='"+geschlecht+"',anrede='"+anrede+"',titel='"+titel+"', land='"+land+"', plz='"+plz+"'," +
                "       ort='"+ort+"', strasse='"+strasse+"', datf=to_timestamp('"+datf+"','DD.MM.YYYY HH24:MI:SS')," +
                "       datt=to_timestamp('"+datt+"','DD.MM.YYYY HH24:MI:SS')" +
                "   where personalid="+personalid;
        try {
            Statement st=db.createStatement();
            st.execute(SQL);            
            st.close();
            refreshTable();
        } catch (SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
    }//GEN-LAST:event_btnEditActionPerformed

    private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnDeleteActionPerformed
        String personalid=txtPersonalid.getText();
        String SQL="";
        try {
            Statement st=db.createStatement();
            SQL="delete from xpersonal where personalid="+personalid;
            st.execute(SQL);
            st.close();
            refreshTable();
        } catch (SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }        
    }//GEN-LAST:event_btnDeleteActionPerformed
    

    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnDelete;
    private javax.swing.JButton btnEdit;
    private javax.swing.JButton btnNew;
    private javax.swing.JComboBox cmbAnrede;
    private javax.swing.JComboBox cmbGeschlecht;
    private javax.swing.JComboBox cmbLand;
    private javax.swing.JComboBox cmbTitel;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JLabel lblAnrede;
    private javax.swing.JLabel lblDatf;
    private javax.swing.JLabel lblDatt;
    private javax.swing.JLabel lblGeburtsdatum;
    private javax.swing.JLabel lblGeschlecht;
    private javax.swing.JLabel lblLand;
    private javax.swing.JLabel lblName;
    private javax.swing.JLabel lblOrt;
    private javax.swing.JLabel lblPLZ;
    private javax.swing.JLabel lblPersonalid;
    private javax.swing.JLabel lblStrasse;
    private javax.swing.JLabel lblTitel;
    private javax.swing.JLabel lblVorname;
    private javax.swing.JTable tblPersonal;
    private javax.swing.JTextField txtDatf;
    private javax.swing.JTextField txtDatt;
    private javax.swing.JTextField txtGeburtsdatum;
    private javax.swing.JTextField txtName;
    private javax.swing.JTextField txtOrt;
    private javax.swing.JTextField txtPLZ;
    private javax.swing.JTextField txtPersonalid;
    private javax.swing.JTextField txtStrasse;
    private javax.swing.JTextField txtVorname;
    // End of variables declaration//GEN-END:variables
    
}
